Grouping with totals/subtotals -- nested Muenchian method
By  Dimitre Novatchev
 
Language  XSLT
Category xml, designpatterns, msxml
Posted 07  May  2001
Updated 07  May  2001
 
Summary
This snippet demonstrates how to group elements and their sub-elements calculating totals and sub-totals. The example has two levels of nesting only, however the demonstrated technique can be generalised for multiple levels.
 
The following question was asked on the microsoft.public.xsl newsgroup:
 
How to transform weights.xml file (see the xml code), generated from recordset, into totals.xml? <code/> and <weight/> are already pre-sorted.
 
Code
 
<?xml version="1.0"?>
<total>
  <code c="2A1669">
    <subtotal>3</subtotal>
    <nr n="712374">2</nr>
    <nr n="714273">1</nr>
  </code>
  <code c="2A1747">
    <subtotal>19</subtotal>
    <nr n="714619">9</nr>
    <nr n="714977">10</nr>
  </code>
  <code c="2A1840">
    <subtotal>32</subtotal>
    <nr n="714977">4.5</nr>
    <nr n="712924">6</nr>
    <nr n="712374">15</nr>
    <nr n="714717">6.5</nr>
  </code>
  <code c="DAPR21">
    <subtotal>26.5</subtotal>
    <nr n="178607">10.5</nr>
    <nr n="187982">5</nr>
    <nr n="301218">3.5</nr>
    <nr n="714273">7.5</nr>
  </code>
</total>
XSL Stylesheet
 
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:key name="kCode" match="code" use="."/>
 <xsl:key name="kNr" match="nr" use="../code"/>
 <xsl:key name="kCodeNr" match="nr" use="concat(../code, '|', .)"/>
 <xsl:template match="node()"/>
  <xsl:template match="/">
   <total>
    <xsl:apply-templates select="/xml/row/code"/>
    <xsl:text>&#xA;</xsl:text>
   </total>
  </xsl:template>
  <xsl:template match="code[count(.|key('kCode',.)[1])=1]">
    <xsl:text>&#xA;</xsl:text>
    <code c="{.}">
      <subtotal><xsl:value-of select="sum(key('kCode',.)/../weight)"/></subtotal>
    <xsl:for-each
       select="key('kNr', .)[count(. | key('kCodeNr', concat(../code, '|', .))[1])=1]">
      <nr n="{.}"><xsl:value-of select="sum(key('kCodeNr', concat(../code, '|', .))/../weight)"/></nr>
      <xsl:text>&#xA;</xsl:text>
    </xsl:for-each>
    </code>
  </xsl:template>
</xsl:stylesheet>
The following should be noted:
1. The Muenchian technique is used to process all different elements (the first of each set of elements having the same value).This is specified in the match attribute of the template:
 
<xsl:template match="code[count(.|key('kCode',.)[1])=1]">
2. The total is just a summation of the "weight" siblings of all <code> elements with the same value:
 
 <subtotal><xsl:value-of select="sum(key('kCode',.)/../weight)"/></subtotal>
3. All different <nr> children of a given <code> element are specified again using the Muenchian method (only the first out of each group of <nr>-s with the same value). To ensure that only the <nr> children of the current <code> element will be specified, a different xsl:key, named kCodeNr is used. This key yields all <code><nr> combinations, where the <nr>-s must be children of the >code<:
 
 <xsl:for-each select="key('kNr', .)[count(. | key('kCodeNr', concat(../code, '|', .))[1])=1]">
4. The sub-total for all children with the same value is just a summation of the "weight" siblings of all the siblings of the current having the same value:
 
<nr n="{.}"><xsl:value-of select="sum(key('kCodeNr', concat(../code, '|', .))/../weight)"/></nr>
XML Source
 
 
XSL Stylesheet
 
 
XML/HTML Result:
 
 
Text Result: